﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Collections.Specialized;
using System.Reflection;
using System.Runtime.InteropServices;
using Excel;
using DataTable = System.Data.DataTable;

namespace AutoUnitTestEngine.Helpers
{
    /// <summary>
    /// Excel文件生成器。根据指定的数据源，生成一个Excel文件。
    /// </summary>
    public class ExcelFileGenerator
    {
        private readonly string m_datafilepath;
        private readonly object oMissing = Missing.Value;
        private int _MaxRowCount = 50000;
        private int m_FooterFieldCount;
        private int m_HeaderFieldCount;

        public int  MaxRowCount
        {
            get { return _MaxRowCount; }
            set { _MaxRowCount = value; }
        }

        /// <summary>
        /// 构造函数
        /// </summary>
        public ExcelFileGenerator(string datafilepath)
        {
            Header = new NameValueCollection();
            Footer = new NameValueCollection();
            m_HeaderFieldCount = 0;
            m_FooterFieldCount = 0;
            m_datafilepath = datafilepath;
        }


        /// <summary>
        /// 构造函数
        /// </summary>
        public ExcelFileGenerator()
        {
            Header = new NameValueCollection();
            Footer = new NameValueCollection();
            m_HeaderFieldCount = 0;
            m_FooterFieldCount = 0;
            m_datafilepath = Environment.GetFolderPath(Environment.SpecialFolder.InternetCache);
        }

        /// <summary>
        /// 显示在文件内的标题
        /// </summary>
        public string Title { get; set; }

        /// <summary>
        /// 显示在数据前的文件头信息
        /// </summary>
        public NameValueCollection Header { get; set; }

        /// <summary>
        /// 显示在数据后的页脚信息
        /// </summary>
        public NameValueCollection Footer { get; set; }

        private int HeaderFieldCount
        {
            set { m_HeaderFieldCount = value; }
        }

        private int FooterFiledCount
        {
            set { m_FooterFieldCount = value; }
        }

        private bool IsExistsColumn(DataTable table, string columnName, ArrayList FieldTable)
        {
            for (int c = 0; c < FieldTable.Count; c++)
            {
                string colName = ((Hashtable) FieldTable[c])["Field"].ToString();
                if (columnName.ToLower() == colName.ToLower())
                {
                    table.Columns[colName].ColumnName = ((Hashtable) FieldTable[c])["ColumnName"].ToString();
                    return true;
                }
            }
            return false;
        }

        /// <summary>
        /// 生成文件头
        /// </summary>
        /// <param name="xlApp"></param>
        /// <param name="dataSheet"></param>
        /// <param name="ColumnCount"></param>
        /// <returns></returns>
        private int GenerateHeader(ApplicationClass xlApp, Worksheet dataSheet, int ColumnCount)
        {
            //生成标题及文件头
            int titleRowCount = 0;
            if (Title != null && Title.Trim().Length > 0)
            {
                titleRowCount = 1;
                int endCell = 65 + ColumnCount - 1;
                string endCellName = Convert.ToChar(endCell) + "1";
                Range titleRange = dataSheet.get_Range("A1", endCellName);
                titleRange.Font.Bold = true;
                titleRange.Select();
                ((Range) xlApp.Selection).MergeCells = true;
                ((Range) xlApp.Selection).HorizontalAlignment = XlHAlign.xlHAlignCenter;
                dataSheet.Cells[1, 1] = Title;
                titleRange = null;
            }
            //文件头
            int temp = 0;
            if (Header.Count > 0)
            {
                if (m_HeaderFieldCount == 0)
                    m_HeaderFieldCount = 2;
                if (Header.Count%2 == 0)
                    temp = Header.Count/2;
                else
                    temp = Header.Count/2 + 1;
                for (int i = 0; i < temp; i++)
                {
                    if (i*2 < Header.Count)
                    {
                        dataSheet.Cells[titleRowCount + 1 + i, 1] = Header.Keys[i*2] + "：";
                        dataSheet.Cells[titleRowCount + 1 + i, 2] = Header[i*2];
                    }
                    if (i*2 + 1 < Header.Count)
                    {
                        dataSheet.Cells[titleRowCount + 1 + i, ColumnCount - 1] = Header.Keys[i*2 + 1] + "：";
                        dataSheet.Cells[titleRowCount + 1 + i, ColumnCount] = Header[i*2 + 1];
                    }
                }
            }

            if (titleRowCount + temp > 0)
                return titleRowCount + temp + 2;
            else
                return 1;
        }

        /// <summary>
        /// 生成文件页脚
        /// </summary>
        /// <param name="dataSheet"></param>
        /// <param name="rowIndex"></param>
        /// <param name="ColumnCount"></param>
        private void GenerateFooter(Worksheet dataSheet, int rowIndex, int ColumnCount)
        {
            int temp = 0;
            rowIndex++;
            if (Footer.Count > 0)
            {
                if (m_FooterFieldCount == 0)
                    m_FooterFieldCount = 2;
                if (Footer.Count%2 == 0)
                    temp = Footer.Count/2;
                else
                    temp = Footer.Count/2 + 1;
                for (int i = 0; i < temp; i++)
                {
                    if (i*2 < Footer.Count)
                    {
                        dataSheet.Cells[rowIndex + i, 1] = Footer.Keys[i*2] + "：";
                        dataSheet.Cells[rowIndex + i, 2] = Footer[i*2];
                    }
                    if (i*2 + 1 < Footer.Count)
                    {
                        dataSheet.Cells[rowIndex + i, ColumnCount - 1] = Footer.Keys[i*2 + 1] + "：";
                        dataSheet.Cells[rowIndex + i, ColumnCount] = Footer[i*2 + 1];
                    }
                }
            }
        }

        /// <summary>
        /// 使用数组方式将数据导出到某个工作表
        /// </summary>
        /// <param name="dataSheet"></param>
        /// <param name="dataTable"></param>
        /// <param name="startRecordIndex"></param>
        /// <param name="xlApp"></param>
        /// <param name="FieldTable">用作表头的字段列表，列表的每项是一个哈希表实例，这个哈希表包含有"Field"和"ColumnName"两个键。</param>
        private void GenerateSheet(ApplicationClass xlApp, Worksheet dataSheet, DataTable dataTable,
                                   int startRecordIndex, ArrayList FieldTable)
        {
            //生成列头
            int colIndex = 0;
            string[] needExportColumns;
            if (FieldTable != null)
                needExportColumns = new string[FieldTable.Count];
            else
                needExportColumns = new string[dataTable.Columns.Count];
            int startRowIndex = 1;
            startRowIndex = GenerateHeader(xlApp, dataSheet, needExportColumns.Length);
            for (int j = 0; j < dataTable.Columns.Count; j++)
            {
                if (FieldTable != null)
                {
                    if (IsExistsColumn(dataTable, dataTable.Columns[j].ColumnName, FieldTable))
                    {
                        dataSheet.Cells[startRowIndex, colIndex + 1] = dataTable.Columns[j].ColumnName;
                        needExportColumns[colIndex] = dataTable.Columns[j].ColumnName;
                        colIndex++;
                    }
                }
                else
                {
                    dataSheet.Cells[startRowIndex, j + 1] = dataTable.Columns[j].ColumnName;
                    needExportColumns[j] = dataTable.Columns[j].ColumnName;
                }
            }
            Range headRange = dataSheet.get_Range("A" + startRowIndex, oMissing);
            headRange = headRange.get_Resize(startRowIndex, dataTable.Columns.Count);
            headRange.Font.Bold = true;
            headRange.Borders.LineStyle = 1;
            headRange.Borders[XlBordersIndex.xlEdgeLeft].Weight = XlBorderWeight.xlThick;
            headRange.Borders[XlBordersIndex.xlEdgeTop].Weight = XlBorderWeight.xlThick;
            headRange.Borders[XlBordersIndex.xlEdgeRight].Weight = XlBorderWeight.xlThick;
            int rowcount = 0;
            if (dataTable.Rows.Count - startRecordIndex > _MaxRowCount)
                rowcount = _MaxRowCount;
            else
                rowcount = dataTable.Rows.Count - startRecordIndex;
            int colcount = needExportColumns.Length;
            var dataAry = new object[rowcount,colcount];
            for (int r = 0; r < rowcount; r++)
            {
                for (int c = 0; c < colcount; c++)
                {
                    dataAry[r, c] = dataTable.Rows[r+startRecordIndex][needExportColumns[c]].ToString();
                }
            }
            Range valueRange = dataSheet.get_Range("A" + Convert.ToString(startRowIndex + 1), oMissing);
            valueRange = valueRange.get_Resize(rowcount, colcount);
            valueRange.Font.Bold = false;
            valueRange.NumberFormat = "@";
            valueRange.Value2 = dataAry;
            valueRange.Borders.LineStyle = 1;

            //生成页脚
            GenerateFooter(dataSheet, rowcount + startRowIndex + 1, needExportColumns.Length);
            headRange = null;
        }

        /// <summary>
        /// 生成Excel文件。
        /// </summary>
        /// <param name="dataTables">将要填充到Excel文件的数据源(N张Sheet的Data集合)</param>
        /// <returns>生成的Excel文件路径</returns>
        private string GenerateExcelFile(List<DataTable> dataTables)
        {
            if (dataTables == null || dataTables.Count == 0)
                throw new ArgumentNullException("dataTables", "要导出的数据源为空");
            //string tempFileName;
            //try
            //{
            //    tempFileName = Path.GetTempFileName();
            //    var fi = new FileInfo(tempFileName);
            //    tempFileName = fi.Name.Replace(fi.Extension, ".xls");
            //}
            //catch
            //{
            //    DateTime dt = DateTime.Now;
            //    tempFileName = dt.Year + dt.Month.ToString() + dt.Day + dt.Hour + dt.Minute + dt.Second + ".xls";
            //}

            
            string FilePath = m_datafilepath;


            var xlApp = new ApplicationClass();
            Workbook workbook = null;
            try
            {
                int actualSheetAmount = 0;
                workbook = xlApp.Workbooks.Add(oMissing);
                foreach (DataTable dt in dataTables)
                {
                    FillDataToExcelSheet(dt, xlApp, actualSheetAmount);
                    actualSheetAmount++;
                }
                if (actualSheetAmount < 3)
                {
                    for (var i = actualSheetAmount + 1; i <= 3; i++)
                    {
                        if (i == 1) continue;
                        ((Worksheet)(xlApp.Worksheets[i])).Delete();
                    }
                }
                workbook.SaveAs(FilePath, oMissing, oMissing, oMissing, oMissing, oMissing,
                                XlSaveAsAccessMode.xlExclusive, oMissing, oMissing, oMissing, oMissing);
            }
            catch
            {
                throw;
            }
            finally
            {
                if (workbook != null)
                    workbook.Close(oMissing, oMissing, oMissing);
                xlApp.Quit();
                Marshal.ReleaseComObject(workbook);
                Marshal.ReleaseComObject(xlApp);
                GC.Collect();
            }
            return FilePath;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="dt"></param>
        /// <param name="xlApp"></param>
        /// <param name="actualSheetAmount"></param>
        private void FillDataToExcelSheet(DataTable dt,ApplicationClass xlApp,int actualSheetAmount)
        {
            if (dt == null || dt.Rows.Count == 0) return;
            //预计需要的工作表数
            int sheetCount = 0;

            if (dt.Rows.Count%_MaxRowCount == 0)
                sheetCount = dt.Rows.Count/_MaxRowCount;
            else
                sheetCount = dt.Rows.Count/_MaxRowCount + 1;
            //循环向工作表中加入数据,如果三个默认工作表已用完,需要增加工作表
            int startRecordIndex = 0;
            for (int i = 0; i < sheetCount; i++)
            {
                Worksheet xlWorksheet;
                if (actualSheetAmount >= 3)
                {
                    var lastsheet = (Worksheet) xlApp.Worksheets[actualSheetAmount];
                    xlWorksheet = (Worksheet) xlApp.Worksheets.Add(oMissing, lastsheet, oMissing, oMissing);
                }
                else
                {
                    xlWorksheet = (Worksheet) xlApp.Worksheets[actualSheetAmount + 1];
                }

                xlWorksheet.Name = dt.TableName + (sheetCount < 2 ? "" : "_" + (1 + i));
                startRecordIndex = _MaxRowCount*i;
                GenerateSheet(xlApp, xlWorksheet, dt, startRecordIndex, null);
            }
        
        }


        /// <summary>
        /// 将数据导出到一个Excel文件中，并返回这个Excel文件的路径
        /// </summary>
        /// <param name="dataTables">将要填充到Excel文件的数据源(N张Sheet的Data集合)</param>
        /// <returns></returns>
        public string ExportDataToExcelFile(List<DataTable> dataTables)
        {
            string excelfile = "";
            try
            {
                excelfile = GenerateExcelFile(dataTables);
            }
            catch
            {
                throw;
            }
            finally
            {
                GC.Collect();
            }
            return excelfile;
        }
    }
}